﻿using CmccTool.KPI.DbTables;
using CmccTool.Services;
using SQLite;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Formats.Asn1;
using System.Globalization;
using System.IO.Compression;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using static CmccTool.KPI.DbTables.KpiTables;

namespace CmccTool.KPI.Services
{
    internal class KpiImpHelper
    {
        // 导入数据到数据库中
        #region 公共变量
        private KpiQuertHelper _kq = new();
        #endregion
        #region 数据库初始化
        /// <summary>
        /// 数据库文件初始化
        /// </summary>
        public static SQLiteConnection? KpiDbChh(string? dbPath = null)
        {
            // 读取数据库路径
            dbPath ??= AppConfig.dbPath;
            if (string.IsNullOrEmpty(dbPath)) { return null; }

            try
            {
                // 创建目录
                dbPath = Path.GetFullPath(dbPath);
                string? dbFd = Path.GetDirectoryName(dbPath);
                if (dbFd != null) { Directory.CreateDirectory(dbFd); }
                // 创建数据库
                SQLiteConnection db = new(dbPath);
                // 创建表
                db.CreateTable<KpiXwy>();
                db.CreateTable<KpiOmc>();
                db.CreateTable<KpiXwyLteLd>();
                db.CreateTable<KpiXwyNrLd>();
                db.CreateTable<KpiXwyNrDx>();
                db.CreateTable<ErrCell>();
                db.CreateTable<ErrNode>();
                db.CreateTable<ErrCellNodeRelation>();
                db.CreateTable<GcCgiInfo>();
                return db;
            }
            catch (Exception)
            {
                return null;
            }
        }
        #endregion

        #region 自定义解压方案
        public static void ExtractWithEncoding(string zipPath, string extractDir)
        {
            // 指定编码（如 UTF-8 或 GBK）
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            using var archive = ZipFile.Open(zipPath, ZipArchiveMode.Read, Encoding.GetEncoding("GB2312"));
            foreach (var entry in archive.Entries)
            {
                string targetPath = Path.Combine(extractDir, entry.FullName);
                entry.ExtractToFile(targetPath, overwrite: true);
            }
        }
        #endregion

        #region 文件名 映射 数据表
        /// <summary>
        /// 分层级解析映射规则（优先级：文件名 → 直接父目录名）
        /// </summary>
        private static (Type EntityType, string tabName, string[] Headers, string[] HeadersNew)? ResolveMappingByPriority(string filePath)
        {

            // 优先级1：文件名（不含扩展名）
            string fileName = Path.GetFileNameWithoutExtension(filePath);

            // 检查是否直接匹配
            if (_tableMapping.TryGetValue(fileName, out var mapping))
                return mapping;


            // 优先级2：直接父目录名（非全路径）
            string? directoryPath = Path.GetDirectoryName(filePath);
            string parentDirName = directoryPath != null
                ? Path.GetFileName(directoryPath)
                : string.Empty; // 或 null（若可接受空值）

            if (!string.IsNullOrEmpty(parentDirName) && _tableMapping.TryGetValue(parentDirName, out mapping))
                return mapping;

            // 遍历所有映射规则，检查文件名或者目录前缀匹配
            foreach (var key in _tableMapping.Keys)
            {
                if (fileName.StartsWith(key, StringComparison.OrdinalIgnoreCase) || parentDirName.StartsWith(key, StringComparison.OrdinalIgnoreCase))
                    return _tableMapping[key];
            }

            return null;  // 未匹配到规则
        }

        // 文件特征映射字典：文件名/目录名 → (实体类型, 原始标题数组, 目标标题数组)
        private static readonly Dictionary<string, (Type EntityType, string tabName, string[] Headers, string[] HeadersNew)>
            _tableMapping = new(StringComparer.OrdinalIgnoreCase)
        {
            // 示例映射规则
            {
                "XWY_LTE",
                (typeof(KpiXwy),"KpiXwy",
                 new[] { "时间","小区名称","分公司","厂家","场景","CGI",
                     "RRC连接最大数", "VOLTE话务量(ERL)", "上行干扰PRB值",
                     "上行空口业务字节数(MB)","下行空口业务字节数(MB)", "上下行忙时最大利用率" },  // CSV原始列名
                 new[] {"日期",  "小区名称","分公司","厂家","场景","CGI", "RRC连接最大数", "ERL", "RSSI", "MB_Up", "MB_Down", "PU"  }
                     )// 目标数据库列名
                 
            },
            {
                "sqlserver",
                (typeof(KpiXwy),"KpiXwy",
                 new[] { "日期","小区名","分公司","厂家","场景","CGI",
                     "用户数", "话务量", "RSSI",
                     "Mb_U","Mb_D", "峰值利用率" },  // CSV原始列名
                 new[] {"日期",  "小区名称","分公司","厂家","场景","CGI", "RRC连接最大数", "ERL", "RSSI", "MB_Up", "MB_Down", "PU"  }
                     )// 目标数据库列名
                 
            },
            {
                "SA",
                (typeof(KpiXwy),"KpiXwy",
                 new[] { "开始时间","小区","基站名称","分公司","厂家", "场景", "CGI",
                     "日峰值RRC连接最大数","VONR话务量","上行PRB干扰均值",
                     "PDCP上行业务字节数(MB)", "PDCP下行业务字节数(MB)","日峰值上下行最大利用率" },  // CSV原始列名
                 new[] {"日期","小区名称", "基站名称","分公司","厂家","场景","CGI", "RRC连接最大数", "ERL", "RSSI", "MB_Up", "MB_Down", "PU"  }
                    )// 目标数据库列名
                 
            },
            {
                "GSM",
                (typeof(KpiXwy),"KpiXwy",
                 new[] {"时间","区县","LAC","CI","小区中文名","基站名称","小区总话务量","流量MB","无线利用率" },  // CSV原始列名
                 new[] {"日期", "分公司","TAC", "CGI", "小区名称", "基站名称",  "ERL", "MB_Down", "PU"  }
                    )// 目标数据库列名
                 
            },
            {
                "TDD_KPI",
                (typeof(KpiOmc),"KpiOmc",
                 new[] {"DATE_ID","EUTRANCELLTDD","DL_GB","UL_GB","H0_RATE",
                        "HOPrepSuccLteIntraF","HOPrepSuccLteInterF","HOExeSuccLteIntraF","HOExeSuccLteInterF",
                        "HoPrepAttLteIntraF","HoPrepAttLteInterF","HoExeAttLteIntraF","HoExeAttLteInterF",
                        "lost_PAT_DL","lost_PAT_UL","all_pat_DL","all_pat_UL","LOST_DL_qci1","LOST_UL_qci1",
                        "Radio_Drop_Rate","Radio_SUCC_RATE","RSSIPUSCH","RRC_USER_MAX" },  // CSV原始列名
                 new[] {"日期","CellName","GB_Down","GB_Up","HoRate",
                        "HoSucc1","HoSucc2","HoSucc3","HoSucc4",
                        "HoTotal1","HoTotal2","HoTotal3","HoTotal4",
                        "LostPackageDLQci1","LostPackageULQci1","AllPackageDLQci1","AllPackageULQci1","LostRateDLQci1","LostRateULQci1",
                        "RadioDropRate","RadioSuccRate","RSSI","User"  }
                    )// 目标数据库列名
                 
            },
            {
                "KPI_Sql",
                (typeof(KpiOmc),"KpiOmc",
                 new[] {"DATE_ID","EUTRANCELLTDD","DL_GB","UL_GB","H0_RATE",
                        "HOPrepSuccLteIntraF","HOPrepSuccLteInterF","HOExeSuccLteIntraF","HOExeSuccLteInterF",
                        "HoPrepAttLteIntraF","HoPrepAttLteInterF","HoExeAttLteIntraF","HoExeAttLteInterF",
                        "lost_PAT_DL","lost_PAT_UL","all_pat_DL","all_pat_UL","LOST_DL_qci1","LOST_UL_qci1",
                        "Radio_Drop_Rate","Radio_SUCC_RATE","RSSIPUSCH","RRC_USER_MAX" },  // CSV原始列名
                 new[] {"日期","CellName","GB_Down","GB_Up","HoRate",
                        "HoSucc1","HoSucc2","HoSucc3","HoSucc4",
                        "HoTotal1","HoTotal2","HoTotal3","HoTotal4",
                        "LostPackageDLQci1","LostPackageULQci1","AllPackageDLQci1","AllPackageULQci1","LostRateDLQci1","LostRateULQci1",
                        "RadioDropRate","RadioSuccRate","RSSI","User"  }
                    )// 目标数据库列名
                 
            },
            {
                "NRKPI",
                (typeof(KpiOmc),"KpiOmc",
                 new[] {"DATE_ID","NRCELL","MAC_UL_GB","MAC_DL_GB","SA_HO_RATE",
                        "HoExeSuccOutInterGnb","HoExeSuccOutIntraGnb","HoPrepAttOutInterGnb","HoExeAttOutIntraGnb",
                        "RADIO_RATE","SA_DROP_RATE","RSSI","RrcConnLevelMaxSa" },  // CSV原始列名
                 new[] {"日期","CellName","GB_Up","GB_Down","HoRate",
                        "HoSucc1","HoSucc2","HoTotal1","HoTotal2",
                        "RadioSuccRate","RadioDropRate","RSSI","User" }
                    )// 目标数据库列名
                 
            },
            {
                "LTE-LD-XB",
                (typeof(KpiXwyLteLd),"KpiXwyLteLd",
                 new[] { "时间", "分公司", "厂家", "场景", "小区名", "CGI", "流量MB", "是否校园" },  // CSV原始列名
                 new[] { "日期范围", "分公司", "厂家", "场景", "小区名", "CGI", "流量MB", "是否校园" }
                    )// 目标数据库列名
                 
            },
            {
                "NR-LD-JT-XB",
                (typeof(KpiXwyNrLd),"KpiXwyNrLd",
                 new[] { "时间","分公司","厂家","场景","小区名","工作频段","CGI","是否校园","室分/宏站","零流量/低流量","是否IDS采集高干扰小区","前6天流量MB","前5天流量MB","前4天流量MB","前3天流量MB","前2天流量MB","前1天流量MB","当天流量MB" },  // CSV原始列名
                 new[] { "日期范围", "分公司", "厂家", "场景", "小区名", "工作频段", "CGI", "是否校园", "基站类型", "质差类型", "是否IDS采集高干扰小区", "MBDay6", "MBDay5", "MBDay4", "MBDay3", "MBDay2", "MBDay1", "MBDay0" }
                    )// 目标数据库列名
                 
            },
            {
                "NR-DXY-XB",
                (typeof(KpiXwyNrDx),"KpiXwyNrDx",
                 new[] {"时间","分公司","厂家","场景","小区名","工作频段","CGI","是否校园","室分/宏站","零效益/低效益","是否IDS采集高干扰小区","前6天流量MB","前5天流量MB","前4天流量MB","前3天流量MB","前2天流量MB","前1天流量MB","当天流量MB","最大三天流量均值（GB)","是否白名单剔除"},  // CSV原始列名
                 new[] {"日期范围","分公司","厂家","场景","小区名","工作频段","CGI","是否校园","基站类型","质差类型","是否IDS采集高干扰小区","MBDay6","MBDay5","MBDay4","MBDay3","MBDay2","MBDay1","MBDay0","GbAvgMax3","是否白名单剔除"}
                    )// 目标数据库列名
                 
            },
            {
                "GcCgiInfo",
                (typeof(GcCgiInfo),"GcCgiInfo",
                 new[]{   "CGI", "ENI", "工参小区", "工参基站" } ,  // CSV原始列名
                 new[] { "CGI", "ENI", "工参小区", "工参基站" }
                    )// 目标数据库列名
                 
            }

        };

        #endregion

        #region 导入文件

        /// <summary>
        /// 批量导入文件到数据库（支持ZIP解压和进度回调）
        /// </summary>
        /// <param name="files">文件列表</param>
        /// <param name="db">目标数据库</param>
        /// <param name="progressCallback">进度回调</param>
        /// <returns></returns>
        internal static int ImportFilesToDatabase(List<string> files, SQLiteConnection db, Action<int> progressCallback)
        {
            int importedCount = 0;
            foreach (string f in files)
            {
                importedCount += RecursiveImportFile(f, db, progressCallback);
            }
            return importedCount;
        }

        /// <summary>
        /// 递归导入文件（支持嵌套ZIP和动态标题映射）
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="db">SQLite数据库连接</param>
        /// <param name="progressCallback">进度回调（每次处理完成时触发）</param>
        /// <returns>导入的总记录数</returns>
        public static int RecursiveImportFile(string filePath, SQLiteConnection db, Action<int> progressCallback, bool IsOutFile = true)
        {
            int importedCount = 0;
            string extension = Path.GetExtension(filePath).ToLower();  // 获取文件扩展名（小写）

            // 处理ZIP文件（递归解压）
            if (extension == ".zip")
            {

                // 创建以ZIP文件名命名的临时解压目录（避免目录冲突）
                string zipName = Path.GetFileNameWithoutExtension(filePath);

                string uniqueDirName = $"{zipName}_{Guid.NewGuid()}"; // 生成唯一目录名
                string extractDir = Path.Combine(Path.GetTempPath(), uniqueDirName, zipName);

                try
                {
                    ZipFile.ExtractToDirectory(filePath, extractDir);  // 解压到临时目录
                    // 遍历解压后的所有文件（包括子目录）
                    //ExtractWithEncoding(filePath, extractDir);// 自定义解压方法
                    foreach (var innerFile in Directory.GetFiles(extractDir, "*", SearchOption.AllDirectories))
                    {
                        // 递归处理内部文件
                        importedCount += RecursiveImportFile(innerFile, db, progressCallback, false);
                    }
                }
                finally
                {
                    Directory.Delete(extractDir, recursive: true);  // 确保临时目录被清理
                }
            }
            // 处理CSV文件（实际导入）
            else if (extension == ".csv")
            {
                // 解析CSV文件的映射规则（优先级：文件名 → 父目录名）
                var mapping = ResolveMappingByPriority(filePath);
                if (mapping.HasValue)
                {
                    // 动态反射调用泛型导入方法
                    // 1. 使用安全获取方法模式（推荐）
                    string funName = "ImportCsvFileGenericStream";
                    MethodInfo method = typeof(KpiImpHelper)
                        .GetMethod(funName, BindingFlags.Static | BindingFlags.NonPublic)
                        ?? throw new InvalidOperationException($"方法 '{funName}' 未找到或不可访问！");

                    MethodInfo generic = method.MakeGenericMethod(mapping.Value.EntityType);

                    // 传入CSV路径、数据库连接、原始标题和目标标题
                    object? result = generic.Invoke(null, [filePath, db,
                        mapping.Value.tabName,
                        mapping.Value.Headers,
                        mapping.Value.HeadersNew
                        ]);

                    // 检查返回值是否为 null
                    importedCount = result != null ? (int)result : 0; // 提供默认值
                }
            }
            if (IsOutFile) { progressCallback?.Invoke(1); }  // 触发进度更新}

            return importedCount;
        }



        private static int ImportCsvFileGenericStream<T>(
            string filePath,
            SQLiteConnection db,
            string tabName,
            string[] headers,
            string[] headersNew)
            where T : ICsvEntityFactory<T>, new()
        {
            int importedCount = 0;
            const int batchSize = 10000;
            var batch = new List<T>(batchSize);
            foreach (var row in CsvHelper.StreamCsvByHeaders(filePath, headers, headersNew))
            {
                T item;
                bool isValid;
                (item, isValid) =  T.CreateFromRow(row); // 转换为实体
                if (isValid) { batch.Add(item); importedCount++; }
                if (batch.Count >= batchSize)
                {
                    db.BeginTransaction();
                    db.InsertAll(batch, "OR REPLACE");
                    db.Commit();
                    batch.Clear();
                }
            }
            if (batch.Count > 0)
            {
                db.BeginTransaction();
                db.InsertAll(batch, "OR REPLACE");
                db.Commit();
            }

            // 更新工参
            if (tabName == "KpiOmc" || tabName == "KpiXwy")
            {
                db.Execute($@"UPDATE {tabName}
                    SET 工参小区 = GcCgiInfo.工参小区,
                    工参基站 = GcCgiInfo.工参基站
                    FROM GcCgiInfo  
                    WHERE {tabName}.isNew = 1 and {tabName}.CGI = GcCgiInfo.CGI ;");

                db.Execute($@"UPDATE {tabName}
                    SET isNew = 0
                    WHERE {tabName}.工参小区 != '';");
            }
            return importedCount;  // 返回实际导入行数
        }

        /// <summary>
        /// 泛型CSV导入方法（实际数据转换和插入）
        /// </summary>
        /// <typeparam name="T">目标实体类型</typeparam>
        private static int ImportCsvFileGeneric<T>(
            string filePath,
            SQLiteConnection db,
            string tabName,
            string[] headers,
            string[] headersNew)
            where T : ICsvEntityFactory<T>, new()
        {
            if (!File.Exists(filePath)) return 0;  // 文件不存在时跳过
            if (db == null) return 0;

            // 调用核心CSV解析方法（传入动态标题规则）
            var data = KpiImpCsvFile<T>(filePath, headers, headersNew, "NA");
            if (data.Count == 0) return 0;

            // 事务批量插入（确保原子性）
            db.RunInTransaction(() =>
            {
                db.InsertAll(data, "OR REPLACE");
                if (tabName == "KpiOmc" || tabName == "KpiXwy")
                {
                    db.Execute($@"UPDATE {tabName}
                    SET 工参小区 = GcCgiInfo.工参小区,
                    工参基站 = GcCgiInfo.工参基站
                    FROM GcCgiInfo  
                    WHERE {tabName}.isNew = 1 and {tabName}.CGI = GcCgiInfo.CGI ;");

                    db.Execute($@"UPDATE {tabName}
                    SET isNew = 0
                    WHERE {tabName}.工参小区 != '';");
                }


            });
            return data.Count;  // 返回实际导入行数
        }

        #endregion

        #region 主导入方法

        /// <summary>
        /// 泛型CSV导入方法（支持动态对象构造与复合主键生成）
        /// </summary>
        /// <typeparam name="T">目标实体类型</typeparam>
        /// <param name="filePath">CSV文件路径</param>
        /// <param name="headers">原始标题数组（可选）</param>
        /// <param name="headersNew">目标标题数组（可选）</param>
        /// <param name="missingValue">缺失值占位符（默认"NA"）</param>
        /// <param name="keyGenerator">复合主键生成器委托（可选）</param>
        /// <returns>解析后的实体列表</returns>
        public static List<T> KpiImpCsvFile<T>(
            string filePath,
            string[]? headers = null,
            string[]? headersNew = null,
            string missingValue = "NA")
            where T : ICsvEntityFactory<T>, new()
        {
            // 1. 参数验证
            if (string.IsNullOrEmpty(filePath))
                throw new ArgumentException("文件路径不能为空");
            if (!File.Exists(filePath))
                throw new FileNotFoundException("文件不存在: " + filePath);

            // 2. 读取CSV原始数据（参考CSV解析通用模式）[1,3](@ref)
            var rows = ReadCsvToDictionary(filePath, headers, headersNew);
            List<T> result = new(rows.Count);

            // 3. 检查是否实现工厂接口（优先使用工厂模式）[1](@ref)
            bool useFactory = typeof(ICsvEntityFactory<T>).IsAssignableFrom(typeof(T));

            // 4. 遍历行数据并构造实体
            foreach (var row in rows)
            {
                T item;
                bool isValid;
                if (useFactory)
                {
                    // ✅ 正确：通过泛型类型参数 T 调用静态方法
                    (item, isValid) = T.CreateFromRow(row);
                }
                else
                {
                    item = new T();
                    isValid = SetPropertiesFromRow(item, row, headersNew, missingValue);
                }
                if (isValid) { result.Add(item); }

            }
            return result;
        }
        #endregion

        #region 辅助方法
        /// <summary>
        /// 读取CSV文件为字典列表
        /// </summary>
        private static List<Dictionary<string, string>> ReadCsvToDictionary(
            string filePath, string[]? headers, string[]? headersNew)
        {
            var rows = CsvHelper.ImportCsvByHeaders(filePath, headers, headersNew);
            return rows;
        }

        /// <summary>
        /// 解析CSV标题行
        /// </summary>
        private static string[] ParseHeaders(string headerLine)
            => [.. headerLine.Split(',').Select(h => h.Trim())];

        /// <summary>
        /// 动态设置实体属性（反射机制）
        /// </summary>
        private static bool SetPropertiesFromRow<T>(
            T item,
            Dictionary<string, string> row,
            string[]? headersNew,
            string missingValue)
        {
            var propertyMap = BuildPropertyMap<T>(headersNew);
            foreach (var prop in propertyMap)
            {
                string columnKey = prop.Value;
                if (!row.TryGetValue(columnKey, out string? fieldValue))
                    continue;

                try
                {
                    if (fieldValue == missingValue)
                    {
                        SetDefaultValue(item, prop.Key);
                    }
                    else
                    {
                        object? convertedValue = ConvertStringToType(
                            fieldValue, prop.Key.PropertyType);
                        prop.Key.SetValue(item, convertedValue);
                    }
                }
                catch (Exception ex)
                {
                    throw new CsvImportException(
                        $"属性 {prop.Key.Name} 转换失败: '{fieldValue}' → {prop.Key.PropertyType.Name}",
                        ex);
                }
            }
            return true;
        }

        /// <summary>
        /// 构建属性-列名映射字典
        /// </summary>
        private static Dictionary<PropertyInfo, string> BuildPropertyMap<T>(string[]? headersNew)
        {
            var map = new Dictionary<PropertyInfo, string>();
            foreach (var prop in typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance))
            {
                if (!prop.CanWrite) continue;

                // 优先使用ColumnAttribute特性中的列名
                var columnAttr = prop.GetCustomAttribute<ColumnAttribute>();
                string targetName = columnAttr?.Name ?? prop.Name;

                // 若提供目标标题则验证匹配
                if (headersNew == null || headersNew.Any(h =>
                    h.Equals(targetName, StringComparison.OrdinalIgnoreCase)))
                {
                    map.Add(prop, targetName);
                }
            }
            return map;
        }

        /// <summary>
        /// 类型安全转换（支持基本类型/日期/枚举/可空类型）
        /// </summary>
        private static object? ConvertStringToType(string value, Type targetType)
        {
            // 处理可空类型
            Type underlyingType = Nullable.GetUnderlyingType(targetType) ?? targetType;

            // 特殊类型处理
            if (underlyingType == typeof(DateTime))
                return DateTime.ParseExact(value, "yyyy-MM-dd", CultureInfo.InvariantCulture);

            if (underlyingType.IsEnum)
                return Enum.Parse(underlyingType, value, true);

            // 通用转换
            return TypeDescriptor.GetConverter(underlyingType).ConvertFromString(value);
        }

        /// <summary>
        /// 设置默认值（值类型为default，引用类型为null）
        /// </summary>
        private static void SetDefaultValue(object? item, PropertyInfo prop)
        {
            if (prop.PropertyType.IsValueType)
                prop.SetValue(item, Activator.CreateInstance(prop.PropertyType));
            else
                prop.SetValue(item, null);
        }


        #endregion

        #region 接口与异常类
        /// <summary>
        /// CSV实体工厂接口（支持静态创建方法）[1](@ref)
        /// </summary>
        public interface ICsvEntityFactory<T> where T : ICsvEntityFactory<T>
        {
            // 声明静态抽象方法
            static abstract (T, bool) CreateFromRow(Dictionary<string, string> row);
        }

        /// <summary>
        /// CSV导入专用异常
        /// </summary>
        public class CsvImportException(string message, Exception inner) : Exception(message, inner)
        {
        }
        #endregion
    }
}
